package org.ssp.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.ssp.base.ResultSetHanlder;
import org.ssp.dao.TravelDao;
import org.ssp.pojo.Travel;
import org.ssp.util.DBUtil;

public class TravelImpl implements TravelDao{
    //1,发布活动
	@Override
	public void addTravel(Travel travel) {
		System.out.println(travel.getFirstimgurl()+"....travelimpl");
		String sql="insert into travel(title,content,datetime,firstimgurl,money) values(?,?,now(),?,?);";
		DBUtil.update(sql, travel.getTitle(),travel.getContent(),travel.getFirstimgurl(),travel.getMoney());
	}
    //3，删除活动
	@Override
	public void deleteTravel(int id) {
		String sql="delete from travel where id=?";
		DBUtil.update(sql, id);
	}
    //3,更细活动
	@Override
	public void updateTravel(Travel travel) {
		String sql="update travel set title=?,content=?,firstimgurl=?,datetime=?,money=? where id=?";
		DBUtil.update(sql, travel.getTitle(),travel.getContent(),travel.getFirstimgurl(),travel.getDatetime(),travel.getMoney(),travel.getId());
	}
	
    //4,查询所有活动
	@Override
	public ArrayList<Travel> findAll(int offset,int rows) {
		String sql="select * from travel limit ?,?";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> travels=new ArrayList<Travel>();

				try {
					while(rs.next()){
						Travel t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
						travels.add(t);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return travels;
			}
		}, offset,rows);
	}
    //5，所有活动计数
	@Override
	public int findAll() {
		String sql="select * from travel";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return total;
			}
		}, null);
	}
	//6，某用户的活动清单
	@Override
	public ArrayList<Travel> findByUid(String uid, int offset, int rows) {
		String sql="select * from travel where id in (select travelid from user_travel where uid=?) limit ?,?;";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> travels=new ArrayList<Travel>();
				try {
					while(rs.next()){
						Travel t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
						travels.add(t);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return travels;
			}
			
		}, uid,offset,rows);
		
	}
	@Override
	public int findByUid(String uid) {
		String sql="select * from travel where id in (select travelid from user_travel where uid=?);";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return total;
			}
		}, uid);
	}
	@Override
	public ArrayList<Travel> find10(int offset,int rows){
		String sql="select * from travel limit ?,?;";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> list=new ArrayList<Travel>();
				try {
					while(rs.next()){
						Travel travel=new Travel();
						travel.setId(rs.getInt("id"));
						travel.setTitle(rs.getString("title"));
						travel.setContent(rs.getString("content"));
						travel.setDatetime(rs.getDate("datetime"));
						travel.setFirstimgurl(rs.getString("firstimgurl"));
						travel.setMoney(rs.getDouble("money"));
						list.add(travel);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, offset,rows);
	}
	//根据id查找活动
	@Override
	public Travel findById(int id){
		String sql="select * from travel where id=?;";
		return (Travel)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				Travel travel=null;
				try {
					if(rs.next()){
						travel=new Travel();
						travel.setId(rs.getInt("id"));
						travel.setTitle(rs.getString("title"));
						travel.setContent(rs.getString("content"));
						travel.setFirstimgurl(rs.getString("firstimgurl"));
						travel.setMoney(rs.getDouble("money"));
						travel.setDatetime(rs.getDate("datetime"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return travel;
			}
		}, id);
	}
	@Override
	public ArrayList<Travel> findAllMobile() {
		String sql="select * from travel order by id desc";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder(){
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> travels=new ArrayList<Travel>();

				try {
					while(rs.next()){
						Travel t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
						travels.add(t);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return travels;
			}
		}, null);
	}
	@Override
	public void insertTo_User_Travel(String openid, int travelid) {
		String sql="insert into user_travel values(?,?)";
		DBUtil.update(sql, openid,travelid);
	}
	@Override
	public ArrayList<Travel> findByOpenid(String openid) {
		String sql="select * from travel where id in (select travelid from user_travel where openid=?)";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> travels=new ArrayList<Travel>();
				try {
					while(rs.next()){
						Travel t=new Travel();
						t.setId(rs.getInt("id"));
						t.setTitle(rs.getString("title"));
						t.setContent(rs.getString("content"));
						t.setDatetime(rs.getDate("datetime"));
						t.setFirstimgurl(rs.getString("firstimgurl"));
						t.setMoney(rs.getDouble("money"));
						travels.add(t);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return travels;
			}
			
		}, openid);
	}
}
